﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace pcsse
{
    public class db_news:db_base
    {

        static db_news dbentity_news;

        public static db_news getDBhandle()
        {
            if (dbentity_news == null)
                dbentity_news = new db_news();
            return dbentity_news;

        }

      
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        public void DelNewsById(string id)
        {
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT ATTACHMENT_ID FROM T_NEWS WHERE NEWS_ID=@ID";
            cmd1.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd1.Parameters["@ID"].Value = id;
            DataTable dt = GetDataTableByCmd(cmd1);
            string a_id = dt.Rows[0][0].ToString();
            
            if (a_id != "-1")//需要删除附件
            {
                DelAttachmentById(a_id);
            }
            SqlCommand cmd2 = new SqlCommand();
            cmd2.CommandText = "DELETE FROM T_NEWS WHERE NEWS_ID=@ID";
            cmd2.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd2.Parameters["@ID"].Value = id;
            ExecuteCmd(cmd2);
        }
        public void DelCategoryById(string id)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "DELETE FROM T_CATEGORY WHERE CATEGORY_ID=@ID";
            cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd.Parameters["@ID"].Value = id;
            ExecuteCmd(cmd);
        }
        public string GetCategoryById(int id)
        {
            int i_id = id;
            if (id == 1)
            {
                string strall;
                strall = "ALL";
                return strall;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT CATEGORY_TITLE FROM T_CATEGORY WHERE CATEGORY_ID=@ID";
            cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd.Parameters["@ID"].Value = id;
            DataTable dt =GetDataTableByCmd(cmd);
            return dt.Rows[0][0].ToString();
        }
        public void DelAttachmentById(string id)
        {
            SqlCommand cmd_get_path = new SqlCommand();
            cmd_get_path.CommandText = "SELECT PATH FROM T_ATTACHMENT WHERE ATTACHMENT_ID=@ID";
            cmd_get_path.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd_get_path.Parameters["@ID"].Value = id;
            DataTable dt = GetDataTableByCmd(cmd_get_path);
            if (File.Exists(dt.Rows[0][0].ToString()))
            {
                File.Delete(dt.Rows[0][0].ToString());
            } 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "DELETE FROM T_ATTACHMENT WHERE ATTACHMENT_ID=@ID";
            cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
            cmd.Parameters["@ID"].Value = id;
            ExecuteCmd(cmd);
        }
        public void AddNews(news_data _news)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO T_NEWS(TITLE,TEXT_CONTENT,CATEGORY_ID,DATE,ATTACHMENT_ID,PERMISSION) VALUES(@TITLE,@CONTENT,@CATEGORY_ID,@DATE,@ATTACHMENT_ID,@PERMISSION)";
            //cmd.Parameters.Add("@TITLE"         , _news.news_title);
            //cmd.Parameters.Add("@CONTENT"       , _news.news_content);
            //cmd.Parameters.Add("@CATEGORY_ID"   , _news.category_id);
            cmd.Parameters.Add(new SqlParameter("@TITLE", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@CONTENT", SqlDbType.Text));
            cmd.Parameters.Add(new SqlParameter("@CATEGORY_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DATE", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@ATTACHMENT_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@PERMISSION", SqlDbType.Int));
            cmd.Parameters["@TITLE"].Value = _news.news_title;
            cmd.Parameters["@CONTENT"].Value = _news.news_content;
            cmd.Parameters["@CATEGORY_ID"].Value = _news.category_id;
            cmd.Parameters["@DATE"].Value = _news.date;
            cmd.Parameters["@ATTACHMENT_ID"].Value = _news.am_id;
            cmd.Parameters["@PERMISSION"].Value = _news.news_permission;
            ExecuteCmd(cmd);
        }
        public void AddCategory(category_data _news)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO T_CATEGORY(CATEGORY_TITLE) VALUES(@TITLE)";
            cmd.Parameters.Add(new SqlParameter("@TITLE", SqlDbType.NVarChar, 50));
            cmd.Parameters["@TITLE"].Value = _news.title;
            ExecuteCmd(cmd);
        }

        public int AddAttachment(string am_path,string name)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO T_ATTACHMENT(NAME,PATH) VALUES(@NAME,@PATH)";
            cmd.Parameters.Add(new SqlParameter("@NAME", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@PATH", SqlDbType.NVarChar, 255));
            cmd.Parameters["@NAME"].Value = name;
            cmd.Parameters["@PATH"].Value = am_path;
            ExecuteCmd(cmd);
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT ATTACHMENT_ID FROM T_ATTACHMENT WHERE PATH=@PATH";
            cmd1.Parameters.Add(new SqlParameter("@PATH", SqlDbType.NVarChar, 255));
            cmd1.Parameters["@PATH"].Value = am_path;
            DataTable dt = GetDataTableByCmd(cmd1);
            return int.Parse(dt.Rows[0][0].ToString());

        }
        public attachment GetAttachmentByID(string id)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT NAME,PATH FROM T_ATTACHMENT WHERE ATTACHMENT_ID=@id";
            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, 50));
            cmd.Parameters["@id"].Value = id;
            DataTable dt = GetDataTableByCmd(cmd);
            attachment am = new attachment();
            am.am_id = int.Parse(id);
            am.am_name = dt.Rows[0][0].ToString();
            am.am_path = dt.Rows[0][1].ToString();
            return am;
        }
        //public string GetCataNameByID(string id)
        //{
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.CommandText = "SELECT CATEGORY_TITLE FROM T_CATEGORY WHERE CATEGORY_ID=@ID";
        //    cmd.Parameters.Add("@ID", id);
        //    return GetDataTableByCmd(cmd).Rows[0][0].ToString();
        //}
        public news_data GetNewsByID(string id)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT TITLE,TEXT_CONTENT,CATEGORY_ID,NEWS_ID,DATE,ATTACHMENT_ID,PERMISSION FROM T_NEWS WHERE NEWS_ID=@text_id";
            cmd.Parameters.Add(new SqlParameter("@text_id", SqlDbType.NVarChar, 50));
            cmd.Parameters["@text_id"].Value = id;
            DataTable dt = GetDataTableByCmd(cmd);
            news_data nd = new news_data();
            nd.news_title = dt.Rows[0][0].ToString();
            nd.news_content = dt.Rows[0][1].ToString();
            nd.category_id = int.Parse(dt.Rows[0][2].ToString());
            nd.news_id = int.Parse(dt.Rows[0][3].ToString());
            nd.date = dt.Rows[0][4].ToString();
            nd.am_id = int.Parse(dt.Rows[0][5].ToString());
            nd.news_permission = int.Parse(dt.Rows[0][6].ToString());
            if (nd.am_id!=-1)
            {
                attachment am = GetAttachmentByID(nd.am_id.ToString());
                nd.am_name = am.am_name;
                nd.am_path = am.am_path;
            }
            else
            {
                nd.am_path = null;
                nd.am_name = null;
            }
            return nd;
        }
        public DataTable GetNewsByCategoryID(string id)
        {
            SqlCommand cmd = new SqlCommand();
            if (id == "1")
            {
                cmd.CommandText = "SELECT * FROM T_NEWS,T_CATEGORY WHERE T_NEWS.CATEGORY_ID=T_CATEGORY.CATEGORY_ID ORDER BY T_NEWS.DATE DESC";
            }
            else 
            {
                cmd.CommandText = "SELECT * FROM T_NEWS,T_CATEGORY WHERE T_NEWS.CATEGORY_ID=T_CATEGORY.CATEGORY_ID AND T_NEWS.CATEGORY_ID=@text_id ORDER BY T_NEWS.DATE DESC";
                cmd.Parameters.Add(new SqlParameter("@text_id", SqlDbType.NVarChar, 50));
                cmd.Parameters["@text_id"].Value = id;
            }
            DataTable dt = GetDataTableByCmd(cmd);
            return dt;
        }
        public void UpdateNews(news_data _news)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "UPDATE T_NEWS SET TITLE = @TITLE,TEXT_CONTENT=@CONTENT,CATEGORY_ID=@CATEGORY_ID,DATE=@DATE,ATTACHMENT_ID=@ATTACHMENT_ID,PERMISSION=@PERMISSION WHERE NEWS_ID = @NEWS_ID ";
            cmd.Parameters.Add(new SqlParameter("@TITLE", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@CONTENT", SqlDbType.Text));
            cmd.Parameters.Add(new SqlParameter("@CATEGORY_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@NEWS_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DATE", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@ATTACHMENT_ID", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@PERMISSION", SqlDbType.Int));
            cmd.Parameters["@TITLE"].Value = _news.news_title;
            cmd.Parameters["@CONTENT"].Value = _news.news_content;
            cmd.Parameters["@CATEGORY_ID"].Value = _news.category_id;
            cmd.Parameters["@NEWS_ID"].Value = _news.news_id;
            cmd.Parameters["@DATE"].Value = _news.date;
            cmd.Parameters["@ATTACHMENT_ID"].Value = _news.am_id;
            cmd.Parameters["@PERMISSION"].Value = _news.news_permission;
            ExecuteCmd(cmd);
        }
        public void UpdatCategory(category_data _cate)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "UPDATE T_CATEGORY SET CATEGORY_TITLE=@title WHERE CATEGORY_ID=@id ";
            cmd.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
            cmd.Parameters["@title"].Value = _cate.title;
            cmd.Parameters["@id"].Value = _cate.id;
            ExecuteCmd(cmd);
        }


    }

    public class news_data
    {
        public string news_title;
        public string news_content;
        public string date;
        public int category_id;
        public int news_id;
        public int am_id;
        public string am_name;
        public string am_path;
        public int news_permission;
    }
    public class category_data
    {
        public int id;
        public string title;

        //public int c_state;
    }
    public class attachment
    {
        public int am_id;
        public string am_name;
        public string am_path;
    }
    //static public class ControlHelper
    //{
    //    static public void AddStyleSheet(Page page, string cssPath)
    //    {
    //        HtmlLink link = new HtmlLink();
    //        link.Href = cssPath;
    //        link.Attributes["rel"] = "stylesheet";
    //        link.Attributes["type"] = "text/css";
    //        page.Header.Controls.Add(link);
    //    }
    //} 
}
